Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# COLVAL

The COLVAL function returns a numeric value from a column to the left of the current column in the same row of a report. COLVAL can only be used in the ROW command and the REPORT command.

Return Value

DECIMAL when the selected column contains numeric or Boolean data; NA when the column (n) contains only a TEXT or ID value; or an error when the specified column is the current column, a column to the right of the current column, or a nonexistent column

Syntax

COLVAL(n)

Arguments

n

  The number of the column in the current row whose value you want; n can be any `INTEGER` expression.

  Use a positive number to identify an absolute column number (counting left to right from the left margin of the report). In figuring an absolute column number, you must count all columns shown in the report. For example, this means that when you are using a REPORT command that produces a column of labels down the left side of the report, you count this column of labels as column 1.For example, `COLVAL(2)` identifies the second column from the left margin of the report.

  Use a negative number to identify a relative column number (counting right to left from the current column). For example, `COLVAL(-2)` identifies the column that is two columns to the left of the current column.

Examples

Example 9-9 Performing Column Calculations in a Report

Suppose in a report you want to show actual sales and planned sales, along with the difference between the two. You can use the COLVAL function to calculate this difference.

    LIMIT month TO 'Jun96'
    LIMIT district TO 'Boston'
    FOR product
       ROW product sales sales.plan COLVAL(2)-COLVAL(3)

These statements produce the following output.

    Tents          95,120.83  80,138.18  14,982.65
    Canoes        157,762.08 132,931.39  24,830.69
    Racquets       97,174.44  84,758.46  12,415.98
    Sportswear     79,630.20  73,568.52   6,061.68
    Footwear      153,688.02 109,219.15  44,468.87